package com.distribute.framework.core.sql;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import com.distribute.framework.core.sql.datasource.Lazy3qDataSource;

import com.distribute.framework.core.web.helper.JsonHelper;
import com.distribute.framework.core.web.util.Lazy3qLogger;
import com.distribute.framework.core.web.util.Page;

public class Lazy3qDaoSupport
{	
	
	private DataSource dataSource;
	public Lazy3qDaoSupport()
	{
		
	}
		
	public Lazy3qDaoSupport(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	/**
	 * 取Connection
	 * @return
	 * @throws SQLException 
	 */
	public Connection getConnection() throws SQLException
	{
		return dataSource.getConnection();
	}
	
	
	/**
	 * 执行存储过程
	 * @param sql
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public List<List> Call(String sql,Object... params)
	{
		List<List> lists=new ArrayList();		
		CallableStatement cst=null;
		Connection conn=null;
		try{			
			conn=dataSource.getConnection();
			Lazy3qLogger.sql(sql);
			Lazy3qLogger.sql(JsonHelper.json(params));
			
			cst=conn.prepareCall(sql);
			if(params.length>0)				
				for(int i=0;i<params.length;i++)
					if(params[i]!=null && params[i] instanceof java.io.InputStream)
						cst.setBinaryStream(i+1, (InputStream) params[i], ((java.io.InputStream)params[i]).available());
					else
						cst.setObject(i+1, params[i]);
			boolean moreResultSets = cst.execute();				
			while (moreResultSets) 
			{
				ResultSet rs = cst.getResultSet();
				ResultSetMetaData md = rs.getMetaData();
				List list=new ArrayList();
				while(rs.next())
				{
					Map map=new SqlMap();
					for(int i=0;i<md.getColumnCount();i++)
					{
						String columnName=md.getColumnName(i+1);
						Object value=rs.getObject(columnName);
						map.put(columnName,value);
					}
					list.add(map);
				}
				rs.close();
				moreResultSets = cst.getMoreResults();
				lists.add(list);
			}
			if(conn.getAutoCommit()==false)
				conn.commit();
		}catch(Exception ex){
			throw new RuntimeException(ex);
		}
		finally{
			try{
				if(cst!=null)
					cst.close();
			} catch (SQLException e){}			
			try
			{
				if(conn!=null)
					conn.close();
			} catch (SQLException e){}
		}
		
		return lists;		 
	}
	
	/**
	 * 执行execute
	 * @param sql
	 * @param params
	 * @return
	 */
	public Boolean execute(String sql,Object... params)
	{
		PreparedStatement pst=null;
		Statement st=null;
		Connection conn=null;
		try{
			
			conn=dataSource.getConnection();
			Lazy3qLogger.sql(sql);
			Lazy3qLogger.sql(JsonHelper.json(params));
			
			if(params.length>0)
			{				
				pst = conn.prepareStatement(sql);				
				for(int i=0;i<params.length;i++){
					if(params[i]!=null && params[i] instanceof java.io.InputStream)
						pst.setBinaryStream(i+1, (InputStream) params[i], ((java.io.InputStream)params[i]).available());
					else
						pst.setObject(i+1, params[i]);
				}
				Boolean bOk = pst.execute();
				if(conn.getAutoCommit()==false)
					conn.commit();
				return bOk;
			}
			else
			{
				st = conn.createStatement();	
								
				boolean bOk = st.execute(sql);
				if(conn.getAutoCommit()==false)
					conn.commit();
				return bOk;
			}		
		}catch(Exception ex){
			throw new RuntimeException(ex);
		}
		finally{
			try{
				if(pst!=null)
					pst.close();
				if(st!=null)
					st.close();
			} catch (SQLException e){}
			try
			{
				if(conn!=null)
					conn.close();
			} catch (SQLException e){}			
		}
	}
	
	
	/**
	 * 执行execute
	 * @param sql
	 * @param params
	 * @return
	 */
	public Boolean executeBatch(List<String> sqls)
	{	
		PreparedStatement pst=null;
		Statement st=null;
		Connection conn=null;
		try{			
			conn=this.getConnection();
			st = conn.createStatement();	
			for(String sql:sqls){
				Lazy3qLogger.sql(sql);
				st.addBatch(sql);				
			}
			st.executeBatch();	
			return true;
		}catch(Exception ex){
			throw new RuntimeException(ex);
		}
		finally{
			try{
				if(pst!=null)
					pst.close();
				if(st!=null)
					st.close();
			} catch (SQLException e){}
			try
			{
				if(conn!=null)
					conn.close();
			} catch (SQLException e){}			
		}
	}
	
	
	/**
	 * 执行更新
	 * @param sql
	 * @param params
	 * @return
	 */
	public Integer executeUpdate(String sql,Object... params)
	{
		PreparedStatement pst=null;
		Statement st=null;
		Connection conn=null;
		try{			
			conn=dataSource.getConnection();
			Lazy3qLogger.sql(sql);
			Lazy3qLogger.sql(JsonHelper.json(params));
			
			if(params.length>0)
			{
				pst = conn.prepareStatement(sql);
				for(int i=0;i<params.length;i++)
				{
					if(params[i]!=null && params[i] instanceof java.io.InputStream)
						pst.setBinaryStream(i+1, (InputStream) params[i], ((java.io.InputStream)params[i]).available());
					else
						pst.setObject(i+1, params[i]);					
				}
				int iUpdate = pst.executeUpdate();	
				if(conn.getAutoCommit()==false)
					conn.commit();
				return iUpdate;
			}
			else
			{	
				st = conn.createStatement();	
				
				int iUpdate = st.executeUpdate(sql);
				if(conn.getAutoCommit()==false)
					conn.commit();
				return iUpdate;
			}		
		}catch(Exception ex){
			throw new RuntimeException(ex);
		}
		finally{
			try{
				if(pst!=null)
					pst.close();
				if(st!=null)
					st.close();
			} catch (SQLException e){}
			try
			{
				if(conn!=null)
					conn.close();
			} catch (SQLException e){}
		}
	}
	
	/**
	 * 执行查询
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<Map> executeQuery(String sql,Object... params)
	{
		return executeQuery(sql,null,params);
	}
	
	/**
	 * 执行查询
	 * @param sql
	 * @param params
	 * @return
	 */
	public List executeQuery(String sql,Class cls,Object... params)
	{
		List list=new ArrayList();
		PreparedStatement pst=null;
		Statement st=null;
		Connection conn=null;
		try{			
			conn=dataSource.getConnection();
			Lazy3qLogger.sql(sql);
			Lazy3qLogger.sql(JsonHelper.json(params));
			
			ResultSet rs=null;
			if(params.length>0)
			{				
				pst = conn.prepareStatement(sql);
				for(int i=0;i<params.length;i++)
				{
					if(params[i]!=null && params[i] instanceof java.io.InputStream)
						pst.setBinaryStream(i+1, (InputStream) params[i], ((java.io.InputStream)params[i]).available());
					else
						pst.setObject(i+1, params[i]);
				}
				rs = pst.executeQuery();
			}
			else
			{
				st = conn.createStatement();	
				//
				rs = st.executeQuery(sql);
			}
			ResultSetMetaData md = rs.getMetaData();
			Boolean baseType=false;//映射类型类基础类型
			if(cls!=null && Pattern.matches("Integer|Long|Double|Byte|Short|Float|Boolean|String", cls.getSimpleName()))
				baseType=true;
			while(rs.next()){
				int columnCount=md.getColumnCount();//返回字段数
				if(columnCount==1 && baseType==true)
					list.add(rs.getObject(1));
				else{
					Map map=new SqlMap();
					for(int i=0;i<md.getColumnCount();i++){
						String columnName=md.getColumnName(i+1);
						Object value=rs.getObject(columnName);
						map.put(columnName,value);
					}
					list.add(cls==null?map:ConvertObject(map,cls));
				}
			}
			if(conn.getAutoCommit()==false)
				conn.commit();
			return list;
		}catch(Exception ex){
			throw new RuntimeException(ex);
		}
		finally{
			try{
				if(pst!=null)
					pst.close();
				if(st!=null)
					st.close();
			} catch (SQLException e){}			
			try
			{
				if(conn!=null)
					conn.close();
			} catch (SQLException e){}
		}		
	}
	
	
	/**
	 * 计算运算表达式
	 * @param expression
	 * @return
	 */
	public Double calculate(String expression){
		Object value = this.executeQuery("select "+expression+" as result").get(0).get("result");		
		return Double.parseDouble(value.toString());		
	}
	
	

	/**
	 * 转Map为对象
	 * @param map
	 * @param cls
	 * @return
	 * @throws Exception 
	 */
	private Object ConvertObject(Map map, Class cls) throws Exception
	{
		Object object = cls.newInstance();
		if(object instanceof Map)
			return map;
		Field[] fields = object.getClass().getDeclaredFields();
		for(Field field:fields){
			String fName=field.getName();
			Object value=map.get(fName);
			field.setAccessible(true);
			field.set(object, value);
		}
		return object;
	}

	/**
	 * 执行分页查询
	 * @param sql
	 * @param params
	 * @return
	 */
	public Page executeQuery(String sql,int page,int size,Object... params)
	{		
		List list=new ArrayList();
		PreparedStatement pst=null;
		Statement st=null;
		Connection conn=null;
		try{			
			conn=dataSource.getConnection();
			Lazy3qLogger.sql(sql);
			Lazy3qLogger.sql(JsonHelper.json(params));
			
			ResultSet rs=null;
			if(params.length>0)
			{				
				pst = conn.prepareStatement(sql);
				for(int i=0;i<params.length;i++)
					if(params[i]!=null && params[i] instanceof java.io.InputStream)
						pst.setBinaryStream(i+1, (InputStream) params[i], ((java.io.InputStream)params[i]).available());
					else
						pst.setObject(i+1, params[i]);
				rs = pst.executeQuery();
			}
			else
			{
				st = conn.createStatement();	
				
				rs = st.executeQuery(sql);
			}
			ResultSetMetaData md = rs.getMetaData();	
						
			rs.last();
			int count=rs.getRow();//总记录数
			
			int skip=(int)Page.test(page,size,count);
			
			int index=0;
			if(count>0){
				rs.absolute(skip+1);
				do
				{
					Map map=new SqlMap();
					for(int i=0;i<md.getColumnCount();i++)
					{
						String columnName=md.getColumnName(i+1);
						Object value=rs.getObject(columnName);
						map.put(columnName,value);
					}
					list.add(map);
				}
				while(rs.next() && ++index<size);
			}
			if(conn.getAutoCommit()==false)
				conn.commit();
			return new Page(page,size,count,list);
		}catch(Exception ex){
			throw new RuntimeException(ex);
		}
		finally{
			try{
				if(pst!=null)
					pst.close();
				if(st!=null)
					st.close();
			} catch (SQLException e){}			
			try
			{
				if(conn!=null)
					conn.close();
			} catch (SQLException e){}
		}
	}	
	
	
	/**
	 * 获取所有表名
	 * @return
	 * @throws Exception
	 */
	public String[] getAllTableNames() throws Exception{
		
		List<String> list=new ArrayList();		
		Statement st=null;
		Connection 	conn=null;	
		try{
			conn=getConnection();			
			DatabaseMetaData meta = conn.getMetaData();												
			//读取所有表
			ResultSet res = meta.getTables(null, null, null ,new String[]{"TABLE"});
	        while (res.next())
	        	list.add(res.getString("TABLE_NAME"));	        
			res.close();			
		}catch(Exception ex){
			throw ex;
		}
		finally{
			try{				
				if(st!=null)
					st.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			try{				
				if(conn!=null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}			
		}
		
		String[] array=new String[list.size()];
		for(int i=0;i<list.size();i++)
			array[i]=list.get(i);
		return array;
	}
	
	/**
	 * 获取所有表
	 * @param needColumns 是否需要字段
	 * @return
	 * @throws Exception 
	 */
	public List<Table> getAllTables(Boolean needColumns) throws Exception {
		String[] tablenames = getAllTableNames();
		List<Table> tables = new ArrayList();
		Connection 	conn=null;	
		try{
			conn=getConnection();		
			DatabaseMetaData meta = conn.getMetaData();		
			
			
			Map<String,String> comments = new HashMap();
			{
				//查询表字段的备注信息
				Statement st=conn.createStatement();
				ResultSet resultSet = st.executeQuery("show table STATUS");
				while (resultSet.next()){
					String columnName = resultSet.getString("Name");
					String strComment = resultSet.getString("Comment");
					comments.put(columnName, strComment);
				}
				st.close();
			}
			
			for(String tableName:tablenames){
				Table table=new Table(tableName);
				table.setTitle(comments.get(tableName));
	        	if(needColumns){
	        		//查询各表的字段
		        	String sql="select * from "+tableName+" where 1=2";
		        	PreparedStatement ps = conn.prepareStatement(sql);
		        	ResultSet rs=null;
		        	try{
		        		rs = ps.executeQuery();
		        	}catch(Exception e){
		        		System.out.println("查询表时出错："+e.getMessage());
		        		return null;
		        	}
			        ResultSetMetaData cmt = rs.getMetaData();
			        for (int i = 1; i <= cmt.getColumnCount(); i++) {
			        	Column column=new Column();
			        	column.setName(cmt.getColumnName(i));//字段名
			        	column.setSqlType(cmt.getColumnTypeName(i));//SQL类型
			        	String javaType=cmt.getColumnClassName(i);//java类型
			        	if(javaType.equals("java.sql.Timestamp")||javaType.equals("java.sql.Date"))
			        		javaType="java.util.Date";
			        	if(column.getSqlType().equals("SMALLINT"))
			        		javaType="java.lang.Short";
			        	column.setType(javaType);//java类型		        	
			        	column.setLength(cmt.getColumnDisplaySize(i));//字段取长度
			        	table.addColumns(column);
			        }
			        
			        //查询非空的字段
			        ResultSet colRet = meta.getColumns(null, "%", table.getName(), "%");
			        while (colRet.next()){
			        	String columnName = colRet.getString("COLUMN_NAME");
			        	int nullable = colRet.getInt("NULLABLE");
			        	Column column=table.getColumn(columnName);
			        	column.setNotNull(nullable==0);
					}
					
		        	
		        	//查询主键字段
					ResultSet pkRs = meta.getPrimaryKeys(null, null, table.getName());
					while (pkRs.next())
					{
						 String pkColumnName = pkRs.getString("COLUMN_NAME");
						 table.setPrimaryKey(pkColumnName);
					}	
					
					//查询表字段的备注信息
					Statement st=conn.createStatement();
					ResultSet fields = st.executeQuery("show full fields from "+tableName);
					while (fields.next()){
						String columnName = fields.getString("Field");
						String strComment = fields.getString("Comment");
						Column column=table.getColumn(columnName);
						column.setComment(strComment);
					}
	        	}
				
				tables.add(table);
			}
			return tables;
		}catch(Exception ex){
			throw ex;
		}
		finally{
			try{				
				if(conn!=null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}			
		}
	}
	
	public Table getTable(String tableName) throws Exception
	{	
		Table table=new Table(tableName);
		
		
		Connection 	conn=null;	
		try{
			conn=getConnection();		
			DatabaseMetaData meta = conn.getMetaData();			        	        	
        	//查询各表的字段
        	String sql="select * from "+tableName+" where 1=2";
        	PreparedStatement ps = conn.prepareStatement(sql);
        	ResultSet rs=null;
        	try{
        		rs = ps.executeQuery();
        	}catch(Exception e){
        		System.out.println("查询表时出错："+e.getMessage());
        		return null;
        	}
        	
	        ResultSetMetaData cmt = rs.getMetaData();
	        for (int i = 1; i <= cmt.getColumnCount(); i++) {
	        	Column column=new Column();
	        	column.setName(cmt.getColumnName(i));//字段名
	        	column.setSqlType(cmt.getColumnTypeName(i));//SQL类型
	        	String javaType=cmt.getColumnClassName(i);//java类型
	        	if(javaType.equals("java.sql.Timestamp")||javaType.equals("java.sql.Date"))
	        		javaType="java.util.Date";
	        	if(column.getSqlType().equals("SMALLINT"))
	        		javaType="java.lang.Short";
	        	if(column.getSqlType().indexOf("BIGINT")>-1)
	        		javaType="java.lang.Long";
	        	if(javaType.indexOf("Decimal")>-1)
	        		javaType="java.lang.Double";
	        	column.setType(javaType);//java类型	
	        	column.setLength(cmt.getColumnDisplaySize(i));//字段取长度
	        	table.addColumns(column);
	        }
	        
	        //查询非空的字段
	        ResultSet colRet = meta.getColumns(null, "%", table.getName(), "%");
	        while (colRet.next()){
	        	String columnName = colRet.getString("COLUMN_NAME");
	        	int nullable = colRet.getInt("NULLABLE");
	        	Column column=table.getColumn(columnName);
	        	column.setNotNull(nullable==0);
			}
			
        	
        	//查询主键字段
			ResultSet pkRs = meta.getPrimaryKeys(null, null, table.getName());
			while (pkRs.next())
			{
				 String pkColumnName = pkRs.getString("COLUMN_NAME");
				 table.setPrimaryKey(pkColumnName);
			}	
			
			//查询表字段的备注信息
			Statement st=conn.createStatement();
			ResultSet fields = st.executeQuery("show full fields from "+tableName);
			while (fields.next()){
				String columnName = fields.getString("Field");
				String strComment = fields.getString("Comment");
				Column column=table.getColumn(columnName);
				column.setComment(strComment);
			}
			
			//获取表名
			table.setTitle(getCommentByTableName(table.getName()));
			
		}catch(Exception ex){
			throw ex;
		}
		finally{
			try{				
				if(conn!=null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}			
		}
		
		return table;
	}
	
	public Boolean existTable(String tableName) throws Exception
	{
		Statement st=null;
		Connection 	conn=null;	
		try{
			conn=getConnection();
			if(conn==null)
				return false;
			DatabaseMetaData meta = conn.getMetaData();
			//读取所有表			
			st=conn.createStatement();
			ResultSet res = meta.getTables(null, null, tableName,new String[]{"TABLE"});
			if(res.next())
				return true;			
		}catch(Exception ex){
			throw ex;
		}
		finally{
			try{				
				if(st!=null)
					st.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			try{				
				if(conn!=null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}		
		}
		return false;
	}

	public void DelTable(String tableName) throws Exception
	{		
		Statement st=null;
		Connection 	conn=null;	
		try{
			conn=getConnection();
			st=conn.createStatement();
			if(existTable(tableName))
			{
				System.out.println("DROP TABLE IF EXISTS "+tableName+";");
				st.execute("DROP TABLE IF EXISTS "+tableName+"");
			}
		}catch(Exception ex){
			throw ex;
		}
		finally{
			try{				
				if(st!=null)
					st.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			try{				
				if(conn!=null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}		
		}
	}

	public void SaveTable(Table table) throws Exception
	{
		if(table==null || table.getColumns().size()<1)
			throw new Exception("无效的表");
		
		Statement st=null;
		Connection 	conn=null;	
		try{
			conn=getConnection();
			st=conn.createStatement();
			if(!existTable(table.getName()))
			{
				String strCreate = getCreateTableSql(table,false);
				st.execute(strCreate);				
			}			
		}catch(Exception ex){
			throw ex;
		}
		finally{
			try{				
				if(st!=null)
					st.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			try{				
				if(conn!=null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}		
		}
	}

	public static String getCreateTableSql(Table table,Boolean bDrop) {
		String strCreate="";
		if(bDrop){
			strCreate+="DROP TABLE IF EXISTS "+table.getName()+";\n";
		}
		strCreate+="CREATE TABLE "+table.getName()+" (\n";
		String strExp = "";
		List<Column> columns = table.getColumns();				
		for(int i=0;i<columns.size();i++)
		{
			if(i>0)
				strCreate+=",\n";
			Column column=columns.get(i);
			strCreate+=" "+column.getName();
			strCreate+=" "+column.getSqlType();
			if(Pattern.matches("VARCHAR|VARBINARY", column.getSqlType()))
				strCreate+=" ("+column.getLength()+")";
			if(column.getNotNull())
				strCreate+=" NOT NULL";
			/*if(column.getName().indexOf("update_time")>-1)
				strCreate+=" DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP";
			if(column.getName().indexOf("add_time")>-1)
				strCreate+=" DEFAULT now()";*/
			if(column.getPrimary()!=null && column.getPrimary()==true)
			{
				strCreate+=" PRIMARY KEY";
				if(!Pattern.matches("VARCHAR|VARBINARY", column.getSqlType())){
					strCreate+=" auto_increment";
				}
			}
			if(column.getComment()!=null && !column.getComment().trim().equals("")){
				strCreate+=" COMMENT '"+column.getComment()+"'";
			}
			if(column.getUnique())
				strExp = ",\n UNIQUE KEY `"+column.getName()+"` (`"+column.getName()+"`)"+strExp;
			else if(column.getKey())
				strExp = ",\n KEY `"+column.getName()+"` (`"+column.getName()+"`)"+strExp;
		}	
		strCreate+=strExp;
		strCreate+="\n)";
		
		strCreate+="COMMENT='"+table.getTitle()+"'";
		strCreate+=";\n";
		System.out.println(strCreate);
		return strCreate;
	}
	

	/** 
     * 获得某表的建表语句 
     * @param tableName 
     * @return 
     * @throws Exception 
     */  
    public String getCommentByTableName(String tableName) throws Exception {  
    	Statement st=null;
		Connection 	conn=null;	
		String comment = "";
		try{
			conn=getConnection();
			st=conn.createStatement();
			
			ResultSet rs = st.executeQuery("SHOW CREATE TABLE " + tableName);  
            if (rs != null && rs.next()) {  
                String createDDL = rs.getString(2);  
                int index = createDDL.indexOf("COMMENT='");  
                if (index < 0) {  
                    return "";  
                }  
                comment = createDDL.substring(index + 9);  
                comment = comment.substring(0, comment.length() - 1);  
            }  
            rs.close();  
		}catch(Exception ex){
			throw ex;
		}
		finally{
			try{				
				if(st!=null)
					st.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			try{				
				if(conn!=null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}		
		}
		return comment;
    }

	public String getCatalog(){
		try{
			return dataSource.getConnection().getCatalog();
		} catch (SQLException e){
			e.printStackTrace();
		}
		return null;
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

}